insert overwrite table jms_dm.dm_outport_effect_detail_dt
select
     waybill_no                   as waybill_no                  --运单号
    ,scan_date                    as scan_date                   --揽收/入仓日期
    ,sender_network_code          as sender_network_code         --寄件网点编码
    ,sender_network_name          as sender_network_name         --寄件网点名称
    ,sender_fran_code             as sender_fran_code            --寄件加盟商编码
    ,sender_fran_name             as sender_fran_name            --寄件加盟商名称
    ,sender_agent_code            as sender_agent_code           --寄件代理区编码
    ,sender_agent_name            as sender_agent_name           --寄件代理区名称
    ,sender_area_code             as sender_area_code            --寄件区县编码
    ,sender_area_name             as sender_area_name            --寄件区县名称
    ,sender_city_code             as sender_city_code            --寄件城市编码
    ,sender_city_name             as sender_city_name            --寄件城市名称
    ,taking_time                  as taking_time                 --揽收时间
    ,warehouse_time               as warehouse_time              --入仓时间
    ,first_center_code            as first_center_code           --始发转运中心发件编码
    ,first_center_name            as first_center_name           --始发转运中心发件名称
    ,first_center_plan_time       as first_center_plan_time      --始发转运中心规划到达时间
    ,first_center_actual_time     as first_center_actual_time    --始发转运中心实际到达时间
    ,first_center_shipment_no     as first_center_shipment_no    --始发转运中心实际任务号
    ,first_center_arrival_time    as first_center_arrival_time   --始发转运中心到件扫描时间
    ,first_center_send_time       as first_center_send_time      --始发转运中心发件扫描时间
    ,last_center_code             as last_center_code            --目的转运中心编码
    ,last_center_name             as last_center_name            --目的转运中心名称
    ,last_network_code            as last_network_code           --目的网点编码
    ,last_network_name            as last_network_name           --目的网点名称
    ,last_fran_code               as last_fran_code              --目的加盟商编码
    ,last_fran_name               as last_fran_name              --目的加盟商名称
    ,last_agent_code              as last_agent_code             --目的代理区编码
    ,last_agent_name              as last_agent_name             --目的代理区名称
    ,last_area_code               as last_area_code              --目的区县编码
    ,last_area_name               as last_area_name              --目的区县名称
    ,last_city_code               as last_city_code              --目的城市编码
    ,last_city_name               as last_city_name              --目的城市名称
    ,if_zhund                     as if_zhund                    --准点件
    ,if_liuc                      as if_liuc                     --留仓件
    ,if_weid                      as if_weid                     --未到件
    ,if_wand                      as if_wand                     --晚到件
    ,customer_code                as customer_code               --客户编号
    ,customer_name                as customer_name               --客户名称
    ,ordersource_code             as ordersource_code            --订单来源编码
    ,ordersource_name             as ordersource_name            --订单来源名称
    ,taking_user_code             as taking_user_code            --揽收业务员编码
    ,taking_user_name             as taking_user_name            --揽收业务员名称
    ,first_nodal_name             as first_nodal_name            --始发集散名称
    ,first_nodal_code             as first_nodal_code            --始发集散编码
    ,first_nodal_actual_time      as first_nodal_actual_time     --实际交件集散时间
    ,first_nodal_send_time        as first_nodal_send_time       --始发集散发件扫描时间
    ,first_nodal_arrival_time     as first_nodal_arrival_time    --始发集散到件扫描时间
    ,first_nodal_bagging_time     as first_nodal_bagging_time    --始发集散建包扫描时间
    ,order_mark_zh                as order_mark_zh               --订单标签中文
    ,dt                           as dt                          --分区时间
from(
    select
          wide.waybill_no                 as waybill_no                  --运单号
         ,wide.scan_date                  as scan_date                   --揽收日期
         ,dim.code                        as sender_network_code         --寄件网点编码
         ,dim.name                        as sender_network_name         --寄件网点名称
         ,dim.fran_code                   as sender_fran_code            --寄件加盟商编码
         ,dim.fran_name                   as sender_fran_name            --寄件加盟商名称
         ,dim.agent_code                  as sender_agent_code           --寄件代理区编码
         ,dim.agent_name                  as sender_agent_name           --寄件代理区名称
         ,dim.area_id                     as sender_area_code            --寄件区县编码
         ,dim.area_desc                   as sender_area_name            --寄件区县名称
         ,dim.city_id                     as sender_city_code            --寄件城市编码
         ,dim.city_desc                   as sender_city_name            --寄件城市名称
         ,wide.taking_time                as taking_time                 --揽收时间
         ,wide.warehouse_time             as warehouse_time              --入仓时间
         ,base.first_center_code          as first_center_code           --始发转运中心发件编码
         ,base.first_center_name          as first_center_name           --始发转运中心发件名称
         ,case when base.first_center_arrival_time is not null or base.first_center_send_time is not null then base.first_center_actual_time end as first_center_actual_time --始发转运中实际到达时间
         ,case when base.first_center_arrival_time is not null or base.first_center_send_time is not null then base.first_center_shipment_no end as first_center_shipment_no --始发转运中实际任务号
         ,base.first_center_arrival_time  as first_center_arrival_time   --始发转运中心到件扫描时间
         ,base.first_center_send_time     as first_center_send_time      --始发转运中心发件扫描时间
         ,first.last_center_code          as last_center_code            --目的转运中心编码
         ,first.last_center_name          as last_center_name            --目的转运中心名称
         ,dim_last.code                   as last_network_code           --目的网点编码
         ,dim_last.name                   as last_network_name           --目的网点名称
         ,dim_last.fran_code              as last_fran_code              --目的加盟商编码
         ,dim_last.fran_name              as last_fran_name              --目的加盟商名称
         ,dim_last.agent_code             as last_agent_code             --目的代理区编码
         ,dim_last.agent_name             as last_agent_name             --目的代理区名称
         ,dim_last.area_id                as last_area_code              --目的区县编码
         ,dim_last.area_desc              as last_area_name              --目的区县名称
         ,dim_last.city_id                as last_city_code              --目的城市编码
         ,dim_last.city_desc              as last_city_name              --目的城市名称
         ,concat(date_add('{{ execution_date | cst_ds }}',config.add_days),config.plan_time) as first_center_plan_time --始发中心规划到车时间
         ,case when base.first_center_actual_time <= concat(date_add('{{ execution_date | cst_ds }}',config.add_days),config.plan_time)
               and (base.first_center_arrival_time is not null or base.first_center_send_time is not null) then 1 else 0 end as if_zhund --准点件
         ,case when (base.first_center_arrival_time is null and base.first_center_send_time is null)
                 or if(base.first_center_arrival_time is not null or base.first_center_send_time is not null,base.first_center_actual_time,'0000-00-00 00:00:00')
                  > if(config.plan_time is not null,concat(date_add('{{ execution_date | cst_ds }}',config.add_days),config.plan_time),'1999-00-00 00:00:00') then 1 else 0 end as if_liuc --留仓件
         ,case when base.first_center_arrival_time is null and base.first_center_send_time is null then 1 else 0 end as if_weid  --未到件
         ,case when if(base.first_center_arrival_time is not null or base.first_center_send_time is not null,base.first_center_actual_time,'0000-00-00 00:00:00') >
                    if(config.plan_time is not null,concat(date_add('{{ execution_date | cst_ds }}',config.add_days),config.plan_time),'1999-00-00 00:00:00') then 1 else 0 end as if_wand  --晚到件
         ,wide.customer_code    as customer_code    --客户编号
         ,wide.customer_name    as customer_name    --客户名称
         ,wide.ordersource_code as ordersource_code --订单来源编码
         ,wide.ordersource_name as ordersource_name --订单来源名称
         ,wide.taking_user_code as taking_user_code --揽收业务员编码
         ,wide.taking_user_name as taking_user_name --揽收业务员名称
         ,first_nodal_name             --始发集散名称
         ,first_nodal_code             --始发集散编码
         ,first_nodal_actual_time      --实际交件集散时间
         ,first_nodal_send_time        --始发集散发件扫描时间
         ,first_nodal_arrival_time     --始发集散到件扫描时间
         ,first_nodal_bagging_time     --始发集散建包扫描时间
         ,order_mark.order_mark_zh
         ,wide.scan_date as dt
    from (
        select
             wide.*
            ,tak.taking_user_code
            ,tak.taking_user_name
        from (
            select
                  waybill_no                  as waybill_no
                 ,dispatch_network_code       as last_network_code   --目的网点编码
                 ,dispatch_network_name       as last_network_name   --目的网点名称
                 ,taking_early_scantime       as taking_time         --最早揽收时间
                 ,warehousing_early_scantime  as warehouse_time      --最早入仓时间
                 ,first_code                  as first_code          --一段码
                 ,nvl(taking_early_scansitecode,warehousing_early_scansitecode)    as early_scan_site_code --收件网点
                 ,least(taking_early_scantime,warehousing_early_scantime)          as scan_time --最早揽收时间
                 ,to_date(least(taking_early_scantime,warehousing_early_scantime)) as scan_date --最早揽收时间
                 ,customer_code           as customer_code      --客户编号
                 ,customer_name           as customer_name      --客户名称
                 ,ordersource_code        as ordersource_code   --订单来源编码
                 ,case when ordersource_name in ('紫金山','逍遥峰','桃花岛','七星潭','桃花岛跨境','桃花岛跨境（逆）','极地湾','紫金山官方代发', '唯品会面单')
                       then ordersource_name else '其他' end as ordersource_name --订单来源名称
            from jms_dwd.dwd_wide_unsign_summary_waybill_dt
            where dt between date_sub('{{ execution_date | cst_ds }}',7) and '{{ execution_date | cst_ds }}'
              and to_date(least(taking_early_scantime,warehousing_early_scantime)) = date_sub('{{ execution_date | cst_ds }}',1)
              and is_intercept <> 1 --剔除拦截件
              and is_end_piece <> 1 --剔除完结件
              and nvl(taking_early_scansitecode,warehousing_early_scansitecode) <> dispatch_network_code --自收自派
        ) wide
        left join (
            select *
            from(
                select
                     waybill_no
                    ,scan_site_code
                    ,deliver_user_code as taking_user_code
                    ,deliver_user      as taking_user_name
                    ,row_number() over(partition by waybill_no order by scan_time asc) as rn 
                from jms_dwd.dwd_tab_barscan_taking_base_hi
                where substr(dt,1,10) between date_sub('{{ execution_date | cst_ds }}',1) and '{{ execution_date | cst_ds }}'
            ) a where a.rn = 1 
        ) tak on wide.waybill_no = tak.waybill_no 
             and wide.early_scan_site_code = nvl(tak.scan_site_code,tak.waybill_no)
    ) wide
    left join (
        select
             waybill_no                 as waybill_no                   --运单号
            ,first_center_send_time     as first_center_send_time       --始发转运中心扫描时间
            ,first_center_arrival_time  as first_center_arrival_time    --始发转运中心扫描时间
            -- ,case when first_center_actual_time = '2099-12-31 00:00:00' then null else first_center_actual_time end as first_center_actual_time --实际首中心到车时间
            ,case when actual_center_arr_time = '2099-12-31 00:00:00' then null else actual_center_arr_time end as first_center_actual_time --实际首中心到车时间
            ,actual_center_shipment_no  as first_center_shipment_no     --实际到车任务号
            ,first_center_send_code     as first_center_code            --始发转运中心发件编码
            ,first_center_send_name     as first_center_name            --始发转运中心发件名称
            ,first_nodal_name           as first_nodal_name             --始发集散名称
            ,first_nodal_code           as first_nodal_code             --始发集散编码
            ,actual_collect_arr_time    as first_nodal_actual_time      --实际交件集散时间
            ,first_nodal_send_time      as first_nodal_send_time        --始发集散发件扫描时间
            ,first_nodal_arrival_time   as first_nodal_arrival_time     --始发集散到件扫描时间
            ,first_nodal_bagging_time   as first_nodal_bagging_time     --始发集散建包扫描时间
        -- from jms_tmp.dwd_tab_barscan_first_center_base_dt --到件发件合并数据
        from jms_dm.dm_tab_barscan_arrival_send_base_hi
        where dt = '{{ execution_date | cst_hour }}'
    ) base on wide.waybill_no = base.waybill_no
    left join (
        select
              code
             ,name
             ,city_id
             ,city_desc
             ,manage_code
             ,manage_name
             ,agent_code
             ,agent_name
             ,fran_code
             ,fran_name
             ,area_id
             ,area_desc
             ,network_type
        from jms_dim.dim_network_whole_massage --网点维度表
    ) dim on wide.early_scan_site_code = dim.code
    left join (
        select
              code
             ,name
             ,city_id
             ,city_desc
             ,manage_code
             ,manage_name
             ,agent_code
             ,agent_name
             ,fran_code
             ,fran_name
             ,area_id
             ,area_desc
             ,network_type
        from jms_dim.dim_network_whole_massage --网点维度表
    ) dim_last on wide.last_network_code = dim_last.code
    left join (
        select
              code        as code
             ,center_code as last_center_code  --目的转运中心编码
             ,center_name as last_center_name  --目的转运中心名称
        from jms_dim.dim_lmdm_sys_first_code
        where is_enable=1
        and is_delete = 1
        group by code,center_name,center_code
    ) first on wide.first_code = first.code
    left join ( -- 获取订单标签
            select
                a.waybill_id
                ,concat_ws(',', collect_set(b.value))       as order_mark_zh  -- 订单标签中文
            from
                jms_dwd.dwd_yl_oms_order_mark_expand_base_dt a
                    left join jms_dim.dim_yl_oms_system_dict_base b on a. field_name = b.label and b.is_delete = 0
            where
                dt between date_add('{{ execution_date | cst_ds }}', -14) and '{{ execution_date | cst_ds }}'
            group by
                a.waybill_id
        ) order_mark
            on wide.waybill_no = order_mark.waybill_id
    left join (
        select
             center_code
            ,center_name
            ,agent_code
            ,agent_name
            ,plan_time
            ,case when nvl(plan_time,' 00:00:00') < ' 18:00:00' then 0 else -1 end as add_days -- 小于18点的才跨1天
        from(
        select
              center_code
             ,center_name
             ,agent_code
             ,agent_name
             ,case when agent_code in (110000)                                 then ' 01:00:00'  -- 110000    北京代理区                                     01:00
                   when agent_code in (120000)                                 then ' 01:00:00'  -- 120000    天津代理区                                     01:00
                   when agent_code in (130000)                                 then ' 01:00:00'  -- 130000    河北代理区                                     01:00
                   when agent_code in (330200) and center_code in ('457401')   then ' 03:00:00'  -- 330200    宁波代理区      457101      宁波转运中心       03:00
                   when agent_code in (330200) and center_code in ('457401A1') then ' 03:00:00'  -- 330200    宁波代理区      457101      宁波转运中心A1     03:00
                   when agent_code in (330200)                                 then ' 01:30:00'  -- 330200    宁波代理区                                     01:30
                   when agent_code in (330100) and center_code in ('457101')   then ' 02:00:00'  -- 330100    杭州代理区      457101      杭州转运中心       02:00
                   when agent_code in (330100) and center_code in ('457302')   then ' 02:00:00'  -- 330100    杭州代理区      457302      嘉兴极兔极嘉分拨   02:00
                   when agent_code in (330100)                                 then ' 01:30:00'  -- 330100    杭州代理区                                     01:30
                   when agent_code in (330001) and center_code in ('457701')   then ' 01:00:00'  -- 330001    温台代理区      457701      温州转运中心       01:00
                   when agent_code in (330001) and center_code in ('457601')   then ' 01:00:00'  -- 330001    温台代理区      457601      台州转运中心       01:00
                   when agent_code in (330001)                                 then ' 01:30:00'  -- 330001    温台代理区                                     01:30
                   when agent_code in (330700)                                 then ' 01:30:00'  -- 330700    金华代理区                                     01:30
                   when agent_code in (500000)                                 then ' 01:30:00'  -- 500000    重庆代理区                                     01:30
                   when agent_code in (330000)                                 then ' 01:30:00'  -- 330000    浙江代理区                                     01:30
                   when agent_code in (530000)                                 then ' 02:00:00'  -- 530000    云南代理区                                     02:00
                   when agent_code in (440001)                                 then ' 01:30:00'  -- 440001    粤东代理区                                     01:30
                   when agent_code in (650000)                                 then ' 01:30:00'  -- 650000    新疆代理区                                     01:30
                   when agent_code in (320002) and center_code in ('351301')   then ' 00:59:00'  -- 320002    苏南代理区      351301      南通转运中心       00:59
                   when agent_code in (320002) and center_code in ('351201')   then ' 22:00:00'  -- 320002    苏南代理区      351201      常熟转运中心       22:00
                   when agent_code in (320002) and center_code in ('351001')   then ' 23:40:00'  -- 320002    苏南代理区      351001      无锡转运中心       23:40
                   when agent_code in (320002) and center_code in ('351901')   then ' 23:30:00'  -- 320002    苏南代理区      351901      常州转运中心       23:30
                   when agent_code in (320001) and center_code in ('351701')   then ' 23:30:00'  -- 320001    苏北代理区      351701      淮安转运中心       23:30
                   when agent_code in (320001) and center_code in ('302501')   then ' 23:00:00'  -- 320001    苏北代理区      302501      南京转运中心       23:00
                   when agent_code in (320001) and center_code in ('351601')   then ' 21:30:00'  -- 320001    苏北代理区      351601      徐州转运中心       21:30
                   when agent_code in (320001) and center_code in ('352301')   then ' 22:00:00'  -- 320001    苏北代理区      352301      泰州转运中心       22:00
                   when agent_code in (310000)                                 then ' 02:00:00'  -- 310000    上海代理区                                     02:00
                   when agent_code in (610000)                                 then ' 23:00:00'  -- 610000    陕西代理区                                     23:00
                   when agent_code in (140000)                                 then ' 01:30:00'  -- 140000    山西代理区                                     01:30
                   when agent_code in (370000)                                 then ' 00:00:00'  -- 370000    山东代理区                                     00:00
                   when agent_code in (640000)                                 then ' 06:00:00'  -- 640000    宁夏代理区                                     06:00
                   when agent_code in (150000) and center_code in ('847101')   then ' 21:00:00'  -- 150000    内蒙古代理区    847101      呼市转运中心       21:00
                   when agent_code in (150000) and center_code in ('847201')   then ' 20:00:00'  -- 150000    内蒙古代理区    847201      包头转运中心       20:00
                   when agent_code in (150000) and center_code in ('847601')   then ' 21:00:00'  -- 150000    内蒙古代理区    847601      赤峰转运中心       21:00
                   when agent_code in (210000)                                 then ' 01:30:00'  -- 210000    辽宁代理区                                     01:30
                   when agent_code in (360000)                                 then ' 01:30:00'  -- 360000    江西代理区                                     01:30
                   when agent_code in (220000)                                 then ' 01:30:00'  -- 220000    吉林代理区                                     01:30
                   when agent_code in (430000)                                 then ' 06:00:00'  -- 430000    湖南代理区                                     06:00
                   when agent_code in (420000)                                 then ' 01:30:00'  -- 420000    湖北代理区                                     01:30
                   when agent_code in (230000)                                 then ' 01:30:00'  -- 230000    黑龙江代理区                                   01:30
                   when agent_code in (410000)                                 then ' 06:00:00'  -- 410000    河南代理区                                     02:00  20230915改为6点
                   when agent_code in (460000)                                 then ' 04:00:00'  -- 460000    海南代理区                                     04:00
                   when agent_code in (520000)                                 then ' 06:00:00'  -- 520000    贵州代理区                                     06:00
                   when agent_code in (450000) and center_code in ('277101')   then ' 03:00:00'  -- 450000    广西代理区      277101      南宁转运中心       03:00
                   when agent_code in (450000) and center_code in ('277201')   then ' 01:00:00'  -- 450000    广西代理区      277201      柳州转运中心       01:00
                   when agent_code in (450000) and center_code in ('277301')   then ' 19:30:00'  -- 450000    广西代理区      277301      桂林转运中心       19:30
                   when agent_code in (440100) and center_code in ('102002')   then ' 06:00:00'  -- 440100    广州代理区      175501      广州里水转运中心   05:30
                   when agent_code in (440100) and center_code in ('102001')   then ' 02:00:00'  -- 440100    广州代理区      175501      广州花都转运中心   05:30
                   when agent_code in (440100) and center_code in ('102003')   then ' 04:00:00'  -- 440100    广州代理区      102003      广州番禺转运中心   00:00
                   when agent_code in (440100)                                 then ' 01:30:00'  -- 440100    广州代理区                                     01:30
                   when agent_code in (440002) and center_code in ('176001')   then ' 04:00:00'  -- 440002    粤西代理区      176001      中山转运中心       04:30
                   when agent_code in (440002) and center_code in ('166801')   then ' 22:00:00'  -- 440002    粤西代理区      166801      茂名转运中心       22:00
                   when agent_code in (440002)                                 then ' 01:30:00'  -- 440002    粤西代理区                                     01:30
                   when agent_code in (441900) and center_code in ('176901')   then ' 05:30:00'  -- 441900    东莞代理区      175501      东莞虎门转运中心   05:30
                   when agent_code in (441900)                                 then ' 06:00:00'  -- 441900    东莞代理区                                     06:00
                   when agent_code in (440300) and center_code in ('175501')   then ' 05:00:00'  -- 440300    深圳代理区      175501      深圳转运中心       04:00
                   when agent_code in (440300)                                 then ' 06:00:00'  -- 440300    深圳代理区                                     06:00
                   when agent_code in (620000)                                 then ' 06:00:00'  -- 620000    甘肃代理区                                     06:00
                   when agent_code in (350000)                                 then ' 01:30:00'  -- 350000    福建代理区                                     01:30
                   when agent_code in (510000)                                 then ' 00:30:00'  -- 510000    川藏代理区                                     00:30
                   when agent_code in (340000)                                 then ' 03:00:00'  -- 340000    安徽代理区                                     03:00
                   when agent_code in (630000)                                 then ' 06:00:00'  -- 630000    青海代理区                                     06:00
                   when agent_code in (540000)                                 then ' 00:30:00'  -- 540000	  西藏代理区                                     00:30
                   end as plan_time
        from jms_dim.dim_network_whole_massage
        group by center_code
                ,center_name
                ,agent_code
                ,agent_name
        ) a
    ) config on config.center_code = base.first_center_code
) base
distribute by dt,pmod(hash(rand()),30);
